package com.coalmine.common.utils.sql;

import com.coalmine.common.exception.UtilException;
import com.coalmine.common.utils.StringUtils;
import org.apache.commons.collections.MapUtils;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * sql操作工具类
 * 
 * @author pyq
 */

public class SqlUtil
{
    /**
     * 定义常用的 sql关键字
     */
    public static String SQL_REGEX = "select |insert |delete |update |drop |count |exec |chr |mid |master |truncate |char |and |declare ";

    /**
     * 仅支持字母、数字、下划线、空格、逗号、小数点（支持多个字段排序）
     */
    public static String SQL_PATTERN = "[a-zA-Z0-9_\\ \\,\\.]+";

    /**
     * 检查字符，防止注入绕过
     */
    public static String escapeOrderBySql(String value)
    {
        if (StringUtils.isNotEmpty(value) && !isValidOrderBySql(value))
        {
            throw new UtilException("参数不符合规范，不能进行查询");
        }
        return value;
    }

    /**
     * 验证 order by 语法是否符合规范
     */
    public static boolean isValidOrderBySql(String value)
    {
        return value.matches(SQL_PATTERN);
    }

    /**
     * SQL关键字检查
     */
    public static void filterKeyword(String value)
    {
        if (StringUtils.isEmpty(value))
        {
            return;
        }
        String[] sqlKeywords = StringUtils.split(SQL_REGEX, "\\|");
        for (String sqlKeyword : sqlKeywords)
        {
            if (StringUtils.indexOfIgnoreCase(value, sqlKeyword) > -1)
            {
                throw new UtilException("参数存在SQL注入风险");
            }
        }
    }
    private static final Pattern PATTERN = Pattern.compile("\\b(\\s*and\\s*|\\s*exec\\s*|\\s*insert\\s*|\\s*select\\s*|\\s*drop\\s*|\\s*grant\\s*|\\s*alter\\s*|\\s*" +
            "delete\\s*|\\s*update\\s*|\\s*count\\s*|\\s*chr\\s*|\\s*mid\\s*|\\s*master\\s*|\\s*truncate\\s*|\\s*char\\s*|\\s*declare\\s*|\\s*or\\s*)\\b");

    /**
     * @Description: sql注入正则校验
     * @Author: pyq
     * @Date: 2022/3/24-14:04
     */
    public static boolean sqlValidate(String str){
        //统一转为小写
        String s = str.toLowerCase();
        //使用pattern.matcher获取匹配器
        Matcher matcher = PATTERN.matcher(s);
        //使用匹配器的matches方法进行正则匹配
        return matcher.find();
    }
    public static boolean sqlParamValidate(Map<String, Object> map) {
        boolean flag = false;
        if (map != null && !map.isEmpty()) {
            for (String key : map.keySet()) {
                Object value = map.get(key);
                if (value instanceof String) {
                    String paramValue = String.valueOf(value);
                    boolean flags = sqlValidate(paramValue);
                    if (flags) {
                        flag = true;
                        break;
                    }
                }
            }
        }
        return flag;
    };
    /**
     * @Description: 校验入参是否有sql注入
     * @Author: pyq
     * @Date: 2022/3/24-15:30
     */
    public static String judgeSqlInject(Map<String, Object> paras) {
        if (MapUtils.isEmpty(paras)) {
            return null;
        }
        for (String key:paras.keySet()) {
            Object val = paras.get(key);
            if (Objects.isNull(val)) {
                continue;
            }
            if (val instanceof List) {
                List<Map<String, Object>> list = (List<Map<String, Object>>)val;
                for (Map<String, Object> map:list) {
                    String value = judgeSqlInject(map);
                    if (!StringUtils.isBlank(value)) {
                        //Log.info("【存在sql注入风险】 {}", key);
                        return key + " 存在sql注入风险";
                    }
                }
            } else if (val instanceof Map) {
                Map<String, Object> map = (Map<String, Object>)val;
                String value = judgeSqlInject(map);
                if (!StringUtils.isBlank(value)) {
                    //Log.info("【存在sql注入风险】 {}", key);
                    return key + " 存在sql注入风险";
                }
            } else {
                String value = String.valueOf(val);
                if (sqlValidate(value)) {
                    //Log.info("【存在sql注入风险】 {}", key);
                    return key + " 存在sql注入风险";
                }
            }
        }
        return null;
    }

    public static String replacer(String data) {
        if (StringUtils.isNoneBlank(data)) {
            try {
                //'%'开始,后面两个字符不是数字，也不是字母
                data = data.replaceAll("%(?![0-9a-zA-Z]{9999,})", "%25");
                data = data.replaceAll("\\+", "%2B");
                data = URLDecoder.decode(data, "UTF-8");
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        return data;
    }

    public static void main(String[] args) throws UnsupportedEncodingException {
        String sql = "2022-02-13 08:00:00";
        //String sql = "%%E5%BC%A0%E4%B8%89";
        //String sql = "%E5%BC%A0%E4%B8%89";
        System.out.println(sqlValidate(sql));
        //filterKeyword(sql);
        //String data = URLDecoder.decode(sql, "UTF-8");
        //System.out.println(data);
    }
}
